﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
   public class MenuDefinitionUtility: BaseUtility
    {
        public MenuDefinitionUtility(DataManager manager)
            : base(manager)
        { }



        public int Insert(string strName, int nParentID, int nModuleID, string strURL, string strQString,
                      bool bVisible, bool bEnabled, int nPosition)
        {

            int nRtn = -1;

           string sql  = "INSERT INTO MenuDefs " +
                "(Name, ParentID, ModuleID, URL, QString, Visible, Enabled, Position) " +
                "VALUES(@Name, @ParentID, @ModuleID, @URL, @QString, @Visible, @Enabled, @Position)" +
                "SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@Name", SqlDbType.NVarChar, 10, strName);
                this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 100, nParentID);
                this.Factory.AddParameter(cmd, "@ModuleID", SqlDbType.Int, 10, nModuleID);
                this.Factory.AddParameter(cmd, "@URL", SqlDbType.NVarChar, 100, strURL);
                this.Factory.AddParameter(cmd, "@QString", SqlDbType.NVarChar, 10, strQString);
                this.Factory.AddParameter(cmd, "@Visible", SqlDbType.Bit, 100, bVisible);
                this.Factory.AddParameter(cmd, "@Enabled", SqlDbType.Bit, 10, bEnabled);
                this.Factory.AddParameter(cmd, "@Position", SqlDbType.Int, 10, nPosition);
              
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }



 

        public void UpdateParentID(int nID, int nParentID)
        {
           
            string sql = "UPDATE MenuDefs " +
                "SET ParentID=@ParentID " +
                "WHERE ID = @ID";


            using (var cmd = this.Factory.GetCommand(sql))
            {
               

                if (nParentID == -1)
                   
                this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, System.DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, nParentID);
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);
                this.Factory.ExecuteNonQuery(cmd);

            }
          
        }
     

        public void Update(int nID, string strName, int nParentID, int nModuleID, string strURL, string strQString,
                            bool bVisible, bool bEnabled, int nPosition)
        {

           string sql= "UPDATE MenuDefs " +
                "SET Name=@Name, ParentID=@ParentID, ModuleID=@ModuleID, URL=@URL, QString=@QString, " +
                "Visible=@Visible, Enabled=@Enabled, Position=@Position " +
                "WHERE ID = @ID";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@Name", SqlDbType.NVarChar, 10, strName);
              
                this.Factory.AddParameter(cmd, "@ModuleID", SqlDbType.Int, 10, nModuleID);
                this.Factory.AddParameter(cmd, "@URL", SqlDbType.NVarChar, 100, strURL);
                this.Factory.AddParameter(cmd, "@QString", SqlDbType.NVarChar, 10, strQString);
                this.Factory.AddParameter(cmd, "@Visible", SqlDbType.Bit, 100, bVisible);
                this.Factory.AddParameter(cmd, "@Enabled", SqlDbType.Bit, 10, bEnabled);
                this.Factory.AddParameter(cmd, "@Position", SqlDbType.Int, 10, nPosition);



                if (nParentID == -1)

                    this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, System.DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, nParentID);

                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);
                this.Factory.ExecuteNonQuery(cmd);
            }
          
        }



        public void Delete(int nID)
        {
          string sql= "DELETE FROM MenuDefs " +
                "WHERE ID = @ID ";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

    

        public DataTable GetAll()
        {
            string sql =
                 "SELECT MenuDefs.* , " +
                 "   CAST(MenuDefs.ID as VARCHAR)+' - '+ MenuDefs.Name As IDNAME, " +
                 "   ModuleDefs.Enabled AS ModuleEnabled, " +
                 "   ModuleDefs.PermissionCode  " +
                 "FROM MenuDefs " +
                 "   INNER JOIN ModuleDefs " +
                 "       ON MenuDefs.ModuleID=ModuleDefs.ID " +
                 "ORDER BY POSITION";
           
           return this.Factory.ExecuteTable(sql);
        }
        public DataTable GetAllSortByNameTelerik()
        {
            string sql =
                    "SELECT MenuDefs.ID , " +
                    "   CAST(MenuDefs.ID as VARCHAR)+' - '+ MenuDefs.Name As ParentName " +
                // "   ModuleDefs.Enabled AS ModuleEnabled, " +
                /// "   ModuleDefs.PermissionCode  " +
                " FROM MenuDefs " +
                    "   INNER JOIN ModuleDefs " +
                    "       ON MenuDefs.ModuleID=ModuleDefs.ID " +
                    "ORDER BY Name";
            return this.Factory.ExecuteTable(sql);
        }

        public DataTable GetAllSortByName()
        {
            string sql =
                   "SELECT MenuDefs.* , " +
                   "   CAST(MenuDefs.ID as VARCHAR)+' - '+ MenuDefs.Name As IDNAME, " +
                   "   ModuleDefs.Enabled AS ModuleEnabled, " +
                   "   ModuleDefs.PermissionCode  " +
                   "FROM MenuDefs " +
                   "   INNER JOIN ModuleDefs " +
                   "       ON MenuDefs.ModuleID=ModuleDefs.ID " +
                   "ORDER BY Name";
            return this.Factory.ExecuteTable(sql);
        }

        public DataTable GetAllWithSameParentID(int nParentID)
        {
            string sql = "SELECT * FROM MenuDefs WHERE ParentID=@ParentID";

            using (var cmd = this.Factory.GetCommand(sql))
            {


                if (nParentID == -1)

                    this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, System.DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@ParentID", SqlDbType.Int, 10, nParentID);
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nParentID);
              
            }
            return this.Factory.ExecuteTable(sql);

        }



        public DataTable GetAllWithParentNameAndModuleName()
        {
            string sql = "SELECT MenuDefs.*, ModuleDefs.Description, " +
                  "MenuDefs_1.Name AS ParentName " +
              "FROM MenuDefs INNER JOIN ModuleDefs ON MenuDefs.ModuleID = ModuleDefs.ID " +
              "LEFT OUTER JOIN MenuDefs AS MenuDefs_1 ON MenuDefs.ParentID = MenuDefs_1.ID ";

            return this.Factory.ExecuteTable(sql);
        }


        //public DataRow GetRecordForID(int nID)
        //{
        //    string sql = "SELECT * FROM MenuDefs WHERE ID=@ID";
         

        //    cmdSelect.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4));
        //    cmdSelect.Parameters["@ID"].Value = nID;

        //    SqlDataAdapter da = new SqlDataAdapter(cmdSelect);

        //    DataSet ds = new DataSet();
        //    da.Fill(ds, "MenuDefs");

        //    if (ds.Tables["MenuDefs"].Rows.Count > 0)
        //        return ds.Tables["MenuDefs"].Rows[0];
        //    else
        //        return null;
        //}

        //public DataRow GetRecordForURL(string strURL)
        //{
        //    string sql = " " +
        //     "SELECT MenuDefs.*, ModuleDefs.Description, ModuleDefs.Description as ModuleName, " +
        //         "MenuDefs_1.Name AS ParentName " +
        //     "FROM MenuDefs INNER JOIN ModuleDefs ON MenuDefs.ModuleID = ModuleDefs.ID " +
        //     "LEFT OUTER JOIN MenuDefs AS MenuDefs_1 ON MenuDefs.ParentID = MenuDefs_1.ID " +
        //     "WHERE MenuDefs.URL = @URL";

          

        //    cmdSelect.Parameters.Add(new SqlParameter("@URL", SqlDbType.VarChar, 100));
        //    cmdSelect.Parameters["@URL"].Value = strURL;

        //    SqlDataAdapter da = new SqlDataAdapter(cmdSelect);

        //    DataSet ds = new DataSet();
        //    da.Fill(ds, "MenuDefs");

        //    if (ds.Tables["MenuDefs"].Rows.Count > 0)
        //        return ds.Tables["MenuDefs"].Rows[0];
        //    else
        //        return null;

        //}
    }
}
